Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Namespace WorkFlow.DAO
    Public Class EmployeeDAO
        Inherits OAMS.DAO.BaseDAO
        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub
        Public Function EMPIsExist(ByVal emp_no As String) As Boolean
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@emp_no", SqlDbType.NVarChar, 20)
            sqlParams(0).Value = emp_no


            Dim sb As New StringBuilder()

            sb.AppendLine(" select * from Users where emp_no =@emp_no")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
        End Function
        Public Function GetEMP(ByVal emp_no As String, ByVal emp_name As String, ByVal emp_name_eng As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            sqlParams(0) = New SqlParameter("@emp_no", SqlDbType.NVarChar, 20)
            sqlParams(0).Value = emp_no
            sqlParams(1) = New SqlParameter("@emp_name", SqlDbType.NVarChar, 45)
            sqlParams(1).Value = emp_name
            sqlParams(2) = New SqlParameter("@emp_name_eng", SqlDbType.NVarChar, 45)
            sqlParams(2).Value = emp_name_eng

            Dim sb As New StringBuilder()

            sb.AppendLine(" select A.*, B.emp_name as CrtName ,C.emp_name as ModiName ")
            sb.AppendLine(" from Users A ")
            sb.AppendLine(" left join Users B on A.CrtUID=B.emp_no")
            sb.AppendLine(" left join Users C on A.ModiUID=C.emp_no")
            sb.AppendLine(" where 1=1 ")

            If emp_no <> "" Then
                sb.AppendLine("and A.emp_no like '%'+ @emp_no+ '%' ")
            End If

            If emp_name <> "" Then
                sb.AppendLine("and A.emp_name like '%'+@emp_name + '%' ")
            End If

            If emp_name_eng <> "" Then
                sb.AppendLine("and A.emp_name_eng like '%' + @emp_name_eng + '%' ")
            End If



            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function
        Public Sub InsertEMP(ByVal emp_no As String, ByVal emp_name As String, ByVal emp_name_eng As String, ByVal ext_no As String, ByVal birthday As String, ByVal org_id As String, ByVal logonid As String, ByVal e_mail As String, ByVal status As String, ByVal come_date As String, ByVal company As String, ByVal quit_date As String, ByVal job_id As String, ByVal sex As String, ByVal UID As String, ByVal Password As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(15) {}
            sqlParams(0) = New SqlParameter("@emp_no", SqlDbType.NVarChar, 20)
            sqlParams(0).Value = emp_no
            sqlParams(1) = New SqlParameter("@emp_name", SqlDbType.NVarChar, 45)
            sqlParams(1).Value = emp_name
            sqlParams(2) = New SqlParameter("@emp_name_eng", SqlDbType.NVarChar, 45)
            sqlParams(2).Value = emp_name_eng
            sqlParams(3) = New SqlParameter("@ext_no", SqlDbType.NVarChar, 20)
            sqlParams(3).Value = ext_no
            sqlParams(4) = New SqlParameter("@birthday", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = birthday
            sqlParams(5) = New SqlParameter("@org_id", SqlDbType.NVarChar, 20)
            sqlParams(5).Value = org_id

            sqlParams(6) = New SqlParameter("@logonid", SqlDbType.NVarChar, 50)
            sqlParams(6).Value = logonid

            sqlParams(7) = New SqlParameter("@e_mail", SqlDbType.NVarChar, 80)
            sqlParams(7).Value = e_mail

            sqlParams(8) = New SqlParameter("@status", SqlDbType.NChar, 1)
            sqlParams(8).Value = status
            sqlParams(9) = New SqlParameter("@come_date", SqlDbType.NVarChar, 100)
            sqlParams(9).Value = come_date
            sqlParams(10) = New SqlParameter("@company", SqlDbType.NVarChar, 50)
            sqlParams(10).Value = company

            sqlParams(11) = New SqlParameter("@quit_date", SqlDbType.NVarChar, 100)
            If quit_date = "" Then
                sqlParams(11).Value = DBNull.Value
            Else
                sqlParams(11).Value = quit_date
            End If


            sqlParams(12) = New SqlParameter("@job_id", SqlDbType.NVarChar, 20)
            sqlParams(12).Value = job_id

            sqlParams(13) = New SqlParameter("@sex", SqlDbType.NVarChar, 1)
            sqlParams(13).Value = sex

            sqlParams(14) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(14).Value = UID


            sqlParams(15) = New SqlParameter("@Password", SqlDbType.NVarChar)
            sqlParams(15).Value = Password


            Dim sb As New StringBuilder()
            sb.AppendLine("INSERT INTO  Users(emp_no,emp_name,emp_name_eng,ext_no,birthday,org_id ")
            sb.AppendLine(",logonid,e_mail,status,come_date,company,quit_date,job_id,sex,CrtUID,CrtDate,Password) ")
            sb.AppendLine("values(@emp_no,@emp_name,@emp_name_eng,@ext_no,@birthday,@org_id,@logonid, ")
            sb.AppendLine("@e_mail,@status,@come_date,@company,@quit_date,@job_id,@sex,@UID,getdate() ,@Password) ")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        Public Sub UpdateEMP(ByVal emp_no As String, ByVal emp_name As String, ByVal emp_name_eng As String, ByVal ext_no As String, ByVal birthday As String, ByVal org_id As String, ByVal logonid As String, ByVal e_mail As String, ByVal status As String, ByVal come_date As String, ByVal company As String, ByVal quit_date As String, ByVal job_id As String, ByVal sex As String, ByVal UID As String, ByVal Password As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(15) {}
            sqlParams(0) = New SqlParameter("@emp_no", SqlDbType.NVarChar, 20)
            sqlParams(0).Value = emp_no
            sqlParams(1) = New SqlParameter("@emp_name", SqlDbType.NVarChar, 45)
            sqlParams(1).Value = emp_name
            sqlParams(2) = New SqlParameter("@emp_name_eng", SqlDbType.NVarChar, 45)
            sqlParams(2).Value = emp_name_eng
            sqlParams(3) = New SqlParameter("@ext_no", SqlDbType.NVarChar, 20)
            sqlParams(3).Value = ext_no
            sqlParams(4) = New SqlParameter("@birthday", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = birthday
            sqlParams(5) = New SqlParameter("@org_id", SqlDbType.NVarChar, 20)
            sqlParams(5).Value = org_id

            sqlParams(6) = New SqlParameter("@logonid", SqlDbType.NVarChar, 50)
            sqlParams(6).Value = logonid

            sqlParams(7) = New SqlParameter("@e_mail", SqlDbType.NVarChar, 80)
            sqlParams(7).Value = e_mail

            sqlParams(8) = New SqlParameter("@status", SqlDbType.NChar, 1)
            sqlParams(8).Value = status
            sqlParams(9) = New SqlParameter("@come_date", SqlDbType.NVarChar, 100)
            sqlParams(9).Value = come_date
            sqlParams(10) = New SqlParameter("@company", SqlDbType.NVarChar, 50)
            sqlParams(10).Value = company

            sqlParams(11) = New SqlParameter("@quit_date", SqlDbType.NVarChar, 100)
            If quit_date = "" Then
                sqlParams(11).Value = DBNull.Value
            Else
                sqlParams(11).Value = quit_date
            End If


            sqlParams(12) = New SqlParameter("@job_id", SqlDbType.NVarChar, 20)
            sqlParams(12).Value = job_id

            sqlParams(13) = New SqlParameter("@sex", SqlDbType.NVarChar, 1)
            sqlParams(13).Value = sex

            sqlParams(14) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(14).Value = UID


            sqlParams(15) = New SqlParameter("@Password", SqlDbType.NVarChar)
            sqlParams(15).Value = Password
            Dim sb As New StringBuilder()
            sb.AppendLine("update   Users set ")
            sb.AppendLine("emp_name=@emp_name, ")
            sb.AppendLine("emp_name_eng=@emp_name_eng, ")
            sb.AppendLine("ext_no=@ext_no, ")
            sb.AppendLine("birthday=@birthday, ")
            sb.AppendLine("org_id=@org_id, ")
            sb.AppendLine("logonid=@logonid, ")
            sb.AppendLine("e_mail=@e_mail, ")
            sb.AppendLine("status=@status, ")
            sb.AppendLine("come_date=@come_date, ")
            sb.AppendLine("company=@company, ")
            sb.AppendLine("quit_date=@quit_date, ")
            sb.AppendLine("job_id=@job_id, ")
            sb.AppendLine("sex=@sex, ")
            sb.AppendLine("ModiUID=@UID, ")
            sb.AppendLine("ModiDate=getdate() ,")
            sb.AppendLine("Password=@Password ")


            sb.AppendLine("where emp_no=@emp_no ")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub

        Public Function GetCodeMapping(ByVal TableName As String, ByVal ColumnName As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sqlParams(0) = New SqlParameter("@TableName", SqlDbType.NVarChar, 50)
            sqlParams(0).Value = TableName
            sqlParams(1) = New SqlParameter("@ColumnName", SqlDbType.NVarChar, 50)
            sqlParams(1).Value = ColumnName
            Dim sb As New StringBuilder()
            sb.AppendLine("SELECT Text,Value,ChnText,EngText FROM CodeMapping ")
            sb.AppendLine("WHERE TableName=@TableName ")
            sb.AppendLine("AND ColumnName=@ColumnName  ")
            sb.AppendLine("AND Active='Y' ")
            sb.AppendLine("ORDER BY Sequence ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)

        End Function
        Public Function GetJobData() As DataTable

            Dim sb As New StringBuilder()

            sb.AppendLine("SELECT JobID,JobName,JobEngName,JobChnName FROM WF_JobTitle ")
            sb.AppendLine("ORDER BY JobLevel ")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)

        End Function

        Public Function GetOrg() As DataTable

            Dim sb As New StringBuilder()

            sb.AppendLine("SELECT OrganizationID as  OrgID,OrganizationName  as  OrgName  ,OrgEngName,OrgChnName From Organization ")
            sb.AppendLine("ORDER BY Parent ")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)


        End Function
        Public Function GetCompany() As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("SELECT CompanyID,CompanyName,CompanyChnName,CompanyEngName From WF_Company ")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)

        End Function
    End Class
End Namespace